package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Letter;
import com.xqgl.dao.LetterDao;
import com.xqgl.util.ConnertionUtil;

public class LetterDaoImp implements LetterDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Letter> getAllUserInfos() {
		ArrayList<Letter> letters = new ArrayList<>();
		String sql = "select * from letter";
		try {
			statement = conn.createStatement();
			ResultSet rst = statement.executeQuery(sql);
			while(rst.next()) {
				Letter letter = new Letter();
				letter.setId(rst.getInt("id"));
				letter.setVillagename(rst.getString("villagename"));
				letter.setLettersource(rst.getString("lettersource"));
				letter.setLettertitle(rst.getString("lettertitle"));
				letter.setLettercontent(rst.getString("lettercontent"));
				letter.setLetterctime(rst.getString("letterctime"));
				letter.setLetterstate(rst.getString("letterstate"));
				letters.add(letter);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return letters;
	}
	
	@Override
	public int add(Letter letter) throws SQLException {
		String sql = "insert into letter(villagename,lettersource,lettertitle,lettercontent,letterctime,letterstate)"
				+ "values(?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, letter.getVillagename());
		ps.setString(2, letter.getLettersource());
		ps.setString(3, letter.getLettertitle());
		ps.setString(4, letter.getLettercontent());
		ps.setString(5, letter.getLetterctime());
		ps.setString(6, letter.getLetterstate());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(Letter letter) throws SQLException {
		String sql = "update letter set villagename = '"+letter.getVillagename()+"', lettersource = '"+letter.getLettersource()+"', lettertitle ='"+letter.getLettertitle()+"',"
				+ "lettercontent = '"+letter.getLettercontent()+"', letterctime = '"+letter.getLetterctime()+"', letterstate = '"+letter.getLetterstate()+"' where id = "+letter.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from letter where id = "+id+" ";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Letter findById(int id) throws SQLException {
		Letter letter = new Letter();
		String sql = "select * from letter where id = ?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		while(set.next()){
			letter.setId(set.getInt("id"));
			letter.setVillagename(set.getString("villagename"));
			letter.setLettersource(set.getString("lettersource"));
			letter.setLettertitle(set.getString("lettertitle"));
			letter.setLetterctime(set.getString("letterctime"));
			letter.setLetterstate(set.getString("letterstate"));
		}
		ps.close();
		return letter;
	}

}
